package bancoperu.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;

import bancoperu.model.Customer;
import bancoperu.util.Converter;

public class CustomerDAO extends BaseDAO {

	
	public Customer insert(Customer vo) throws Exception {
		String query = "INSERT INTO Customer(CustomerID, CustomerPassword, PasswordDate, LoginAttempts) VALUES (?,?,?,?)";
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			con = ConexionBD.obtenerConexion();
			stmt = con.prepareStatement(query);
			stmt.setString(1, vo.getCustomerId());
			stmt.setString(2, vo.getCustomerPassword());
			stmt.setDate(3, Converter.utilDateToSqlDate(vo.getPasswordDate()));
			stmt.setInt(4, vo.getLoginAttempts());
			
			int i = stmt.executeUpdate();
			if (i != 1) {
				throw new SQLException("No se pudo insertar");
			}			

		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new Exception(e.getMessage());
		} finally {
			this.cerrarResultSet(rs);
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
		return vo;
	}
	
	public Customer Deposit(Customer vo) throws Exception {
		String query = "INSERT INTO Customer(CustomerID, CustomerPassword, PasswordDate, LoginAttempts) VALUES (?,?,?,?)";
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			con = ConexionBD.obtenerConexion();
			stmt = con.prepareStatement(query);
			stmt.setString(1, vo.getCustomerId());
			stmt.setString(2, vo.getCustomerPassword());
			stmt.setDate(3, Converter.utilDateToSqlDate(vo.getPasswordDate()));
			stmt.setInt(4, vo.getLoginAttempts());
			
			int i = stmt.executeUpdate();
			if (i != 1) {
				throw new SQLException("No se pudo insertar");
			}			

		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new Exception(e.getMessage());
		} finally {
			this.cerrarResultSet(rs);
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
		return vo;
	}
	
	public Customer checkcustomer(String customerID, String CustomerPassword) throws Exception {

		String query = "SELECT CustomerID, CustomerPassword, LoginAttempts, PasswordDate FROM Customer WHERE CustomerID = ?   ";
		Customer vo = new Customer();
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			con = ConexionBD.obtenerConexion();
			stmt = con.prepareStatement(query);
			stmt.setString(1, customerID);
			
			rs = stmt.executeQuery();
			
			if (rs.next()) {
				
				
				/////// check if its unlock
				
				if (rs.getInt("LoginAttempts") > 3){
					throw new Exception("CustomerID Lock!");
				}
				
				
				///////////////////////
				
				vo.setCustomerId(rs.getString("CustomerID"));
				vo.setCustomerPassword(rs.getString("CustomerPassword"));
				vo.setLoginAttempts(rs.getInt("LoginAttempts"));
				vo.setPasswordDate(rs.getDate("PasswordDate"));
			} else{
				
				throw new Exception("customerID incorrect");
				
			}
			
			
			//////////////////////
			
				if( ! CustomerPassword.equals(vo.getCustomerPassword()))
				{
					
					String query2 = "UPDATE Customer SET LoginAttempts = ? WHERE CustomerID = ?";
					Connection con2 = null;
					PreparedStatement stmt2 = null;
					try {
						con2 = ConexionBD.obtenerConexion();
						stmt2 = con2.prepareStatement(query2);
						
						stmt2.setInt(1, vo.getLoginAttempts()+1);
						stmt2.setString(2, vo.getCustomerId());
						
						int i = stmt2.executeUpdate();
						if (i != 1) {
							throw new SQLException("No se pudo actualizar");
						}
					} catch (SQLException e) {
						System.err.println(e.getMessage());
						throw new Exception(e.getMessage());
					} finally {
						this.cerrarStatement(stmt);
						this.cerrarConexion(con);
				}
				
				throw new Exception("Password incorrect");
			}
				
				
			if(CustomerPassword.equals(vo.getCustomerPassword()))
			{
				
				String query3 = "UPDATE Customer SET LoginAttempts = 0 WHERE CustomerID = ?";
				Connection con3 = null;
				PreparedStatement stmt3 = null;
				try {
					con3 = ConexionBD.obtenerConexion();
					stmt3 = con3.prepareStatement(query3);
					stmt3.setString(1, vo.getCustomerId());
					int i = stmt3.executeUpdate();
					if (i != 1) {
						throw new SQLException("No se pudo actualizar");
					}
				} catch (SQLException e) {
					System.err.println(e.getMessage());
					throw new Exception(e.getMessage());
				} finally {
					this.cerrarStatement(stmt);
					this.cerrarConexion(con);
			}
			}
		}
			catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new Exception(e.getMessage());
		} finally {
			this.cerrarResultSet(rs);
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
		return vo;
	}
	
	public Customer retrieveCustomer(String customerID) throws Exception {
		String query = "SELECT CustomerID, CustomerPassword, PasswordDate, LoginAttempts FROM Customer WHERE CustomerID = ?";
		Customer vo = new Customer();
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			con = ConexionBD.obtenerConexion();
			stmt = con.prepareStatement(query);
			stmt.setString(1, customerID);
			rs = stmt.executeQuery();
			if (rs.next()) {				
				vo.setCustomerId(rs.getString("CustomerID"));
				vo.setCustomerPassword(rs.getString("CustomerPassword"));
				vo.setPasswordDate(rs.getDate("PasswordDate"));
				vo.setLoginAttempts(rs.getInt("LoginAttempts"));				
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new Exception(e.getMessage());
		} finally {
			this.cerrarResultSet(rs);
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
		return vo;
	}
	
	public Collection<Customer> getAllCustomers() throws Exception {
		String query = "SELECT CustomerID, CustomerPassword, PasswordDate, LoginAttempts FROM Customer";
		Collection<Customer> lista = new ArrayList<Customer>();
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			con = ConexionBD.obtenerConexion();
			stmt = con.prepareStatement(query);
			rs = stmt.executeQuery();
			while (rs.next()) {
				Customer vo = new Customer();
				vo.setCustomerId(rs.getString("CustomerID"));
				vo.setCustomerPassword(rs.getString("CustomerPassword"));
				vo.setPasswordDate(Converter.sqlDateToUtilDate(rs.getDate("PasswordDate")));
				vo.setLoginAttempts(rs.getInt("LoginAttempts"));
				lista.add(vo);
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new Exception(e.getMessage());
		} finally {
			this.cerrarResultSet(rs);
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
		System.out.println(lista.size());
		return lista;
	}
	
	public Customer update(Customer vo) throws Exception {
		String query = "UPDATE Customer SET CustomerPassword=?, PasswordDate=?, LoginAttempts=? WHERE CustomerID=?";
		Connection con = null;
		PreparedStatement stmt = null;
		try {
			con = ConexionBD.obtenerConexion();
			stmt = con.prepareStatement(query);
			stmt.setString(1, vo.getCustomerPassword());
			stmt.setDate(2, new java.sql.Date(vo.getPasswordDate().getTime()));
			stmt.setInt(3, vo.getLoginAttempts());
			stmt.setString(4, vo.getCustomerId());
			int i = stmt.executeUpdate();
			if (i != 1) {
				throw new SQLException("No se pudo actualizar");
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new Exception(e.getMessage());
		} finally {
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
		return vo;
	}
	
	public Customer updateattempts(Customer vo) throws Exception {
		String query = "UPDATE Customer SET LoginAttempts=? WHERE CustomerID=?";
		Connection con = null;
		PreparedStatement stmt = null;
		try {
			con = ConexionBD.obtenerConexion();
			stmt = con.prepareStatement(query);
			stmt.setString(1, vo.getCustomerPassword());
			stmt.setDate(2, new java.sql.Date(vo.getPasswordDate().getTime()));
			stmt.setInt(3, vo.getLoginAttempts()+1);
			stmt.setString(4, vo.getCustomerId());
			int i = stmt.executeUpdate();
			if (i != 1) {
				throw new SQLException("No se pudo actualizar");
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new Exception(e.getMessage());
		} finally {
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
		return vo;
	}
	
	public void remove(String customerID) throws Exception {
		String query = "DELETE FROM Customer WHERE CustomerID=?";
		Connection con = null;
		PreparedStatement stmt = null;
		try {
			con = ConexionBD.obtenerConexion();
			stmt = con.prepareStatement(query);
			stmt.setString(1, customerID);
			int i = stmt.executeUpdate();
			if (i != 1) {
				throw new SQLException("No se pudo eliminar");
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new Exception(e.getMessage());
		} finally {
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
	}
	
	public void updatePassword(String customerID, String customerPassword, String newPassword) throws Exception {
		customerPassword = newPassword;
		String query = "UPDATE Customer SET CustomerPassword=? WHERE CustomerID=?";
		Connection con = null;
		PreparedStatement stmt = null;

		try {
			
			con = ConexionBD.obtenerConexion();
			stmt = con.prepareStatement(query);
			stmt.executeQuery(query);
			int i = stmt.executeUpdate();
			if (i != 1) {
				throw new SQLException("No se pudo actualizar");
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new Exception(e.getMessage());
		} finally {
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
		
		
	}
	
}
